/*
Navicat MySQL Data Transfer

Source Server         : localhost
Source Server Version : 50624
Source Host           : localhost:3306
Source Database       : cloud_backend

Target Server Type    : MYSQL
Target Server Version : 50624
File Encoding         : 65001

Date: 2018-06-17 09:59:22
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for black_ip
-- ----------------------------
DROP TABLE IF EXISTS `black_ip`;
CREATE TABLE `black_ip` (
  `ip` varchar(32) NOT NULL COMMENT '黑名单ip',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  PRIMARY KEY (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='ip黑名单表';

-- ----------------------------
-- Records of black_ip
-- ----------------------------

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `parentId` int(11) NOT NULL COMMENT '父菜单id',
  `name` varchar(64) NOT NULL COMMENT '菜单名',
  `url` varchar(1024) DEFAULT NULL COMMENT '菜单url',
  `css` varchar(32) DEFAULT NULL COMMENT '菜单样式',
  `sort` int(11) NOT NULL COMMENT '排序',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  `updateTime` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COMMENT='菜单表';

-- ----------------------------
-- Records of menu
-- ----------------------------
INSERT INTO `menu` VALUES ('1', '0', '系统设置', '', 'fa-gears', '1', '2018-01-23 10:20:30', '2018-01-23 10:20:31');
INSERT INTO `menu` VALUES ('2', '1', '菜单', 'pages/menu/menuList.html', 'fa-windows', '2', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('3', '1', '角色', 'pages/role/roleList.html', 'fa-cubes', '3', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('4', '1', '权限', 'pages/permission/permissionList.html', 'fa-align-justify', '4', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('5', '0', '用户管理', '', 'fa-user', '4', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('6', '5', '用户查询', 'pages/user/userList.html', 'fa-user', '4', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('7', '0', '文件查询', 'pages/file/fileList.html', 'fa-folder-open', '5', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('8', '0', '邮件管理', 'pages/mail/mailList.html', 'fa-envelope', '6', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('9', '0', '注册中心', 'http://local.register.com:8761', 'fa-institution', '7', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('10', '0', '监控中心', 'http://local.monitor.com:9001', 'fa-spinner', '8', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('11', '0', 'swagger文档', 'pages/swagger/api-doc.html', 'fa-file-pdf-o', '8', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('12', '0', '黑名单ip', 'pages/blackIP/blackIPList.html', 'fa-child', '9', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('13', '0', '日志查询', 'pages/log/logList.html', 'fa-reorder', '10', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('14', '0', '短信历史查询', 'pages/sms/smsList.html', 'fa-reorder', '11', '2018-01-23 14:04:40', '2018-01-23 14:04:43');
INSERT INTO `menu` VALUES ('15', '1', 'client管理', 'pages/client/clientList.html', 'fa-tachometer',  '13', '2018-01-23 14:04:40', '2018-01-23 14:04:43');

-- ----------------------------
-- Table structure for role_menu
-- ----------------------------
DROP TABLE IF EXISTS `role_menu`;
CREATE TABLE `role_menu` (
  `roleId` int(11) NOT NULL COMMENT '角色id',
  `menuId` int(11) NOT NULL COMMENT '菜单id',
  PRIMARY KEY (`roleId`,`menuId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='角色菜单关系表';

-- ----------------------------
-- Records of role_menu
-- ----------------------------
INSERT INTO `role_menu` VALUES ('1', '1');
INSERT INTO `role_menu` VALUES ('1', '2');
INSERT INTO `role_menu` VALUES ('1', '3');
INSERT INTO `role_menu` VALUES ('1', '4');
INSERT INTO `role_menu` VALUES ('1', '5');
INSERT INTO `role_menu` VALUES ('1', '6');
INSERT INTO `role_menu` VALUES ('1', '7');
INSERT INTO `role_menu` VALUES ('1', '8');
INSERT INTO `role_menu` VALUES ('1', '9');
INSERT INTO `role_menu` VALUES ('1', '10');
INSERT INTO `role_menu` VALUES ('1', '11');
INSERT INTO `role_menu` VALUES ('1', '12');
INSERT INTO `role_menu` VALUES ('1', '13');
INSERT INTO `role_menu` VALUES ('1', '14');
INSERT INTO `role_menu` VALUES ('1', '15');

-- ----------------------------
-- Table structure for t_mail
-- ----------------------------
DROP TABLE IF EXISTS `t_mail`;
CREATE TABLE `t_mail` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
  `userId` int(11) NOT NULL COMMENT '发送人id',
  `username` varchar(50) NOT NULL COMMENT '发送人用户名',
  `toEmail` varchar(128) NOT NULL COMMENT '收件人邮件地址',
  `subject` varchar(255) NOT NULL COMMENT '标题',
  `content` longtext NOT NULL COMMENT '正文',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '0草稿，1成功，2失败',
  `sendTime` datetime DEFAULT NULL COMMENT '发送时间',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  `updateTime` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),
  KEY `updateTime` (`updateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='邮件发送记录表';

-- ----------------------------
-- Records of t_mail
-- ----------------------------




oracle

CREATE TABLE black_ip (
  ip varchar(32) NOT NULL PRIMARY KEY,
  createTime date NOT NULL
)

comment on table black_ip is 'ip黑名单表';
comment on column black_ip.ip is '黑名单ip';
comment on column black_ip.createTime is '创建时间';


CREATE TABLE menu (
  "id" number(11) NOT NULL PRIMARY KEY,
  parentId number(11) NOT NULL,
  "name" varchar(64) NOT NULL,
  url varchar(1024) DEFAULT NULL,
  css varchar(32) DEFAULT NULL,
  "sort" number(11) NOT NULL,
  createTime date NOT NULL,
  updateTime date NOT NULL
)
comment on table menu is '菜单表';
comment on column menu."id" is '自增id';
comment on column menu.parentId is '父菜单id';
comment on column menu."name" is '菜单名';
comment on column menu.url is '菜单url';
comment on column menu.css is '菜单样式';
comment on column menu."sort" is '排序';
comment on column menu.createTime is '创建时间';
comment on column menu.updateTime is '更新时间';
CREATE SEQUENCE menu_seq INCREMENT BY 1 START WITH 15 NOMAXVALUE NOCYCLE NOCACHE;
CREATE OR REPLACE TRIGGER menu_TRG
  BEFORE INSERT ON menu
  FOR EACH ROW  WHEN (new."id" is null)
BEGIN
  SELECT menu_seq.NEXTVAL INTO :NEW."id" FROM DUAL;
END;
INSERT INTO menu VALUES (1, 0, '系统设置', '', 'fa-gears', 1, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (2, 1, '菜单', 'pages/menu/menuList.html', 'fa-windows', 2, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (3, 1, '角色', 'pages/role/roleList.html', 'fa-cubes', 3, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (4, 1, '权限', 'pages/permission/permissionList.html', 'fa-align-justify', 4, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (5, 0, '用户管理', '', 'fa-user', 4, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (6, 5, '用户查询', 'pages/user/userList.html', 'fa-user', 4, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (7, 0, '文件查询', 'pages/file/fileList.html', 'fa-folder-open', 5, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (8, 0, '邮件管理', 'pages/mail/mailList.html', 'fa-envelope', 6, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (9, 0, '注册中心', 'http://local.register.com:8761', 'fa-institution', 7, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (10, 0, '监控中心', 'http://local.monitor.com:9001', 'fa-spinner', 8, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (11, 0, 'swagger文档', 'pages/swagger/api-doc.html', 'fa-file-pdf-o', 8, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (12, 0, '黑名单ip', 'pages/blackIP/blackIPList.html', 'fa-child', 9, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (13, 0, '日志查询', 'pages/log/logList.html', 'fa-reorder', 10, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (14, 0, '短信历史查询', 'pages/sms/smsList.html', 'fa-reorder', 11, to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));
INSERT INTO menu VALUES (15, 1, 'client管理', 'pages/client/clientList.html', 'fa-tachometer',  13,to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ), to_date ( '2015-04-03 16:51:38' , 'YYYY-MM-DD HH24:MI:SS' ));


CREATE TABLE role_menu (
  roleId number(11) NOT NULL,
  menuId number(11) NOT NULL
)
drop table role_menu;

comment on table role_menu is '角色菜单关系表';
comment on column role_menu.roleId is '角色id';
comment on column role_menu.menuId is '菜单id';
INSERT INTO role_menu VALUES (1, 1);
INSERT INTO role_menu VALUES (1, 2);
INSERT INTO role_menu VALUES (1, 3);
INSERT INTO role_menu VALUES (1, 4);
INSERT INTO role_menu VALUES (1, 5);
INSERT INTO role_menu VALUES (1, 6);
INSERT INTO role_menu VALUES (1, 7);
INSERT INTO role_menu VALUES (1, 8);
INSERT INTO role_menu VALUES (1, 9);
INSERT INTO role_menu VALUES (1, 10);
INSERT INTO role_menu VALUES (1, 11);
INSERT INTO role_menu VALUES (1, 12);
INSERT INTO role_menu VALUES (1, 13);
INSERT INTO role_menu VALUES (1, 14);
INSERT INTO role_menu VALUES (1, 15);



CREATE TABLE t_mail (
  "id" number(11) NOT NULL  PRIMARY KEY,
  userId number(11) NOT NULL,
  username varchar(50) NOT NULL,
  toEmail varchar(128) NOT NULL,
  subject varchar(255) NOT NULL,
  "content" varchar2(4000) NOT NULL,
  status number(1) DEFAULT 0,
  sendTime date DEFAULT NULL,
  createTime date NOT NULL,
  updateTime date NOT NULL
)
comment on table t_mail is '邮件发送记录表';
comment on column t_mail."id" is '自增id';
comment on column t_mail.parentId is '发送人id';
comment on column t_mail."name" is '发送人用户名';
comment on column t_mail.url is '收件人邮件地址';
comment on column t_mail.css is '标题';
comment on column t_mail."content" is '正文';
comment on column t_mail.status is '0草稿，1成功，2失败';
comment on column t_mail.sendTime is '发送时间';
comment on column t_mail.createTime is '创建时间';
comment on column t_mail.updateTime is '更新时间';

CREATE SEQUENCE t_mail_seq INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE NOCACHE;
CREATE OR REPLACE TRIGGER t_mail_TRG
  BEFORE INSERT ON t_mail
  FOR EACH ROW  WHEN (new."id" is null)
BEGIN
  SELECT t_mail_seq.NEXTVAL INTO :NEW."id" FROM DUAL;
END;